import pymysql as mysql
import pandas as pd
import datetime
today = datetime.datetime.now().strftime('%Y-%m-%d')
host = "localhost"
user = "s"
passwd = "s"
#passwd = "sdh478174277"
# passwd = "songdonghui"
db = "brfdb"
urine_path_ubuntu = "~/brfDB/brf-db/model/urine.xlsx"
urine_path_windows = "D:/DeskTop/brf-db/model/urine.xlsx"
conn = mysql.connect(host=host,user=user,password=passwd,database=db)
df_base = pd.read_excel(urine_path_ubuntu)
df = df_base.astype(object).where(pd.notnull(df_base),None)
urine_index = {
    'name': df.loc[1][2],
    'sex' : df.loc[1][6],
    'age' : df.loc[1][10],
    'dates' : df.loc[1][14],
    'form' : df.loc[3][10],
    'department': df.loc[2][6],
    'doctor' : df.loc[2][10],
    'description' : df.loc[3][2],
    'wbc' : df.loc[5][3],
    'wbc1' : df.loc[5][9],
    'tt' : df.loc[6][3],
    'tt1' : df.loc[6][9],
    'yxsy' : df.loc[7][3],
    'yxsy1' : df.loc[7][9],
    'ndy' : df.loc[8][3],
    'ndy1' : df.loc[8][9],
    'dhs' : df.loc[9][3],
    'dhs1' : df.loc[9][9],
    'dbz' : df.loc[10][3],
    'dbz1' : df.loc[10][9],
    'ptt' : df.loc[11][3],
    'ptt1' : df.loc[11][9],
    'nbz' : df.loc[12][9],
    'yx' : df.loc[13][3],
    'yx1' : df.loc[13][9],
    'ph' : df.loc[14][9]
}
sql = "INSERT INTO urine VALUES(id,'{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format \
    (urine_index['name'],urine_index['sex'],urine_index['age'],today,urine_index['form'],urine_index['department'], \
    urine_index['doctor'],urine_index['description'],urine_index['wbc'],urine_index['wbc1'],urine_index['tt'],urine_index['tt1'], \
    urine_index['yxsy'],urine_index['yxsy1'],urine_index['ndy'], urine_index['ndy1'],\
    urine_index['dhs'],urine_index['dhs1'],urine_index['dbz'],urine_index['dbz1'],urine_index['ptt'],urine_index['ptt1'], \
    urine_index['nbz'],urine_index['yx'],urine_index['yx1'],urine_index['ph']\
    )
cursor = conn.cursor()
#cursor.execute("./tools.sql")
try:
    cursor.execute(sql)
    conn.commit()
except:
    conn.rollback()
# data = cursor.fetchall()
# print(data)
cursor.close()
conn.close()
